﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Xml;
using System.Data.SqlClient;
using System.IO;

namespace IntermediateExam2DbAndXml
{
    class Pets
    {
        public const string INPORT_FILE_NAME= "../../../pets.xml";
        public const string EXPORT_FILE_NAME = "../../../owners.xml";
        public const string CONECTION_STRING = "Server=.\\SQLEXPRESS;Database=Pets; " +
                                               "Integrated Security=true";
            
        public static SortedDictionary<string, SortedSet<string>> GetPets(string fileName) 
        {
            SortedDictionary<string, SortedSet<string>> pets = new SortedDictionary<string, SortedSet<string>>();
            try
            {
                XDocument xmlDoc = XDocument.Load(fileName);
                var petElements =
                  from pet in xmlDoc.Descendants("pet")
                  select new
                  {
                      petkind = pet.Attribute("petkind").Value,
                      owner = pet.Attribute("owner").Value
                  };
                
                foreach (var pet in petElements)
                {
                    if (pets.ContainsKey(pet.petkind))
                    {
                        pets[pet.petkind].Add(pet.owner);
                    }
                    else
                    {
                        pets.Add(pet.petkind, new SortedSet<string>());
                        pets[pet.petkind].Add(pet.owner);
                    }
                }
            }
            catch (FileNotFoundException)
            {
                throw new FileNotFoundException("Invalid file name");
            }
            return pets;
        }

        public static void ExportTodXml(SortedDictionary<string, SortedSet<string>> pets, string filename) 
        {
            string fileName = filename;
            Encoding encoding = Encoding.UTF8;
            using (XmlTextWriter writer = new XmlTextWriter(fileName, encoding))
            {
                writer.Formatting = Formatting.Indented;
                writer.IndentChar = '\t';
                writer.Indentation = 1;

                writer.WriteStartDocument();
                writer.WriteStartElement("petkinds");
                foreach (var pet in pets)
                {
                    WritePetKind(pet,writer);
                }
                writer.WriteEndDocument();
            }
        }

        private static void WritePetKind(KeyValuePair<string, SortedSet<string>> pet, XmlTextWriter writer)
        {
            writer.WriteStartElement("petkind");
            writer.WriteAttributeString("type", pet.Key);
            foreach (var owner in pet.Value)
            {
                writer.WriteStartElement("owner");
                writer.WriteAttributeString("name", owner);
                writer.WriteEndElement();
            }
            writer.WriteEndElement();
        }

        public static void ExportTodDb(SortedDictionary<string, SortedSet<string>> pets, string conectionString) 
        {
            SqlConnection con = new SqlConnection(conectionString);
            con.Open();
            using(con)
            {
                foreach (var pet in pets)
                {
                    SqlCommand cmdInsertPetKind = new SqlCommand(
                        "INSERT INTO PetKinds(Name) " +
                        "VALUES (@name)", con);
                    cmdInsertPetKind.Parameters.Add(new SqlParameter("@name", pet.Key));
                    cmdInsertPetKind.ExecuteNonQuery();
                    SqlCommand cmdSelectIdentity =
                    new SqlCommand("SELECT @@Identity", con);
                    int insertedRecordId =
                        (int)(decimal)cmdSelectIdentity.ExecuteScalar();
                    foreach (var owner in pet.Value)
                    {
                        SqlCommand cmdInsertOwner = new SqlCommand(
                        "INSERT INTO Owners(Name, PetKindID) " +
                        "VALUES (@name, @PetKindID)", con);
                        cmdInsertOwner.Parameters.Add(new SqlParameter("@name", owner));
                        cmdInsertOwner.Parameters.Add(new SqlParameter("@PetKindID", insertedRecordId));
                        cmdInsertOwner.ExecuteNonQuery();
                    }
                }
                
            }

        }

        static void Main()
        {
            SortedDictionary<string, SortedSet<string>> pets = GetPets(INPORT_FILE_NAME);
            ExportTodXml(pets, EXPORT_FILE_NAME);
            ExportTodDb(pets, CONECTION_STRING);
        }
    }
}
